
Create  PROCEDURE [dbo].[STISendElectronicTaxOffices]
@ReportType BIT=0,
@BeginDate VARCHAR(10),
@EndDate  VARCHAR(10)
AS
BEGIN
  IF @ReportType=0 
  Begin

    ;WITH RawData AS (
     
        SELECT 
            Acc_Code,
            Dbo.MiladiToShamsi(Tarikh) AS ShDate,
            Note,
            SUM(CASE WHEN Bedeh <> 0 THEN Bedeh ELSE 0 END) AS Bedeh,
            SUM(CASE WHEN Bestan <> 0 THEN Bestan ELSE 0 END) AS Bestan
        FROM ##Kol_Moein_Tafzil
        GROUP BY Acc_Code, Dbo.MiladiToShamsi(Tarikh), Note
        HAVING SUM(Bedeh) <> 0 OR SUM(Bestan) <> 0
    ),
    Aggregated AS (
        SELECT 
            RD.ShDate,
            LEFT(RD.Acc_Code, 1) AS TotalAccountCode,
            A1.Acc_Name AS TotalAccountName,
            LEFT(RD.Acc_Code, 3) AS CertainAccountCode,
            A2.Acc_Name AS CertainAccountName,
            RD.Note,
            RD.Bedeh,
            RD.Bestan
        FROM RawData RD
        LEFT JOIN Account A1 ON A1.Acc_Code = LEFT(RD.Acc_Code, 1)
        LEFT JOIN Account A2 ON A2.Acc_Code = LEFT(RD.Acc_Code, 3)
    ),
    WithRowNumber AS (
        SELECT 
            DENSE_RANK() OVER (ORDER BY Note) AS RowNumber,
            ShDate,
            TotalAccountCode,
            TotalAccountName,
            CertainAccountCode,
            CertainAccountName,
            Note,
            Bedeh,
            Bestan
        FROM Aggregated
    )

    SELECT 
        RowNumber,
        ShDate,
        TotalAccountCode,
        cast(TotalAccountName as nvarchar(90)) TotalAccountName,
        CertainAccountCode,
        cast(CertainAccountName as nvarchar(150) ) CertainAccountName,
        cast(Note as nvarchar(150)) Note,
        Bedeh,
        Bestan
    FROM WithRowNumber
    ORDER BY RowNumber, ShDate, TotalAccountCode;
    END
  
    IF @ReportType=1
    BEGIN
     ;WITH RawData AS (
    SELECT
        a.Acc_Code,
        dbo.MiladiToShamsi(@EndDate) AS ShDate,
        ISNULL(s.BEDEH, 0) AS Bedeh,
        ISNULL(s.BESTAN, 0) AS Bestan,
        CASE WHEN ISNULL(s.BEDEH, 0) > 0 THEN s.BEDEH ELSE s.BESTAN END AS AmountAbs
    FROM HeadS h WITH (NOLOCK)
    INNER JOIN Sanad s WITH (NOLOCK) ON h.Id_Sanad = s.Id_Sanad
    INNER JOIN Account a WITH (NOLOCK) ON s.Acc_Code = a.Acc_Code
    WHERE a.Acc_Level = 3
        AND CONVERT(VARCHAR(10), h.Tarikh_User, 111) >= @BeginDate
      AND CONVERT(VARCHAR(10), h.Tarikh_User, 111) <= @EndDate 
      AND (ISNULL(s.BEDEH, 0) <> 0 OR ISNULL(s.BESTAN, 0) <> 0)
),
WithAccountInfo AS (
    SELECT
        RD.ShDate,
        LEFT(RD.Acc_Code, 1) AS TotalAccountCode,
        A1.Acc_Name AS TotalAccountName,
        LEFT(RD.Acc_Code, 3) AS CertainAccountCode,
        A2.Acc_Name AS CertainAccountName,
        RD.Bedeh,
        RD.Bestan,
        RD.AmountAbs
    FROM RawData RD
    LEFT JOIN Account A1 ON A1.Acc_Code = LEFT(RD.Acc_Code, 1) AND A1.Acc_Level = 1
    LEFT JOIN Account A2 ON A2.Acc_Code = LEFT(RD.Acc_Code, 3) AND A2.Acc_Level = 2
),
GroupedRepeated AS (
   
    SELECT
        AmountAbs,
        TotalAccountCode,
        TotalAccountName,
        CertainAccountCode,
        CertainAccountName,
        ShDate,
        SUM(Bedeh) AS SumBedeh,
        SUM(Bestan) AS SumBestan,
        COUNT(*) AS RepeatCount
    FROM WithAccountInfo
    GROUP BY 
        AmountAbs,
        TotalAccountCode,
        TotalAccountName,
        CertainAccountCode,
        CertainAccountName,
        ShDate
    HAVING COUNT(*) > 1  
),
NonGrouped AS (
  
    SELECT
        AmountAbs,
        TotalAccountCode,
        TotalAccountName,
        CertainAccountCode,
        CertainAccountName,
        ShDate,
        Bedeh AS SumBedeh,
        Bestan AS SumBestan
    FROM WithAccountInfo wi
    WHERE NOT EXISTS (
        SELECT 1 
        FROM GroupedRepeated gr 
        WHERE gr.AmountAbs = wi.AmountAbs 
          AND gr.CertainAccountCode = wi.CertainAccountCode
    )
),
AllData AS (
    SELECT AmountAbs, TotalAccountCode, TotalAccountName, CertainAccountCode, CertainAccountName, ShDate, SumBedeh, SumBestan
    FROM GroupedRepeated
    UNION ALL
    SELECT AmountAbs, TotalAccountCode, TotalAccountName, CertainAccountCode, CertainAccountName, ShDate, SumBedeh, SumBestan
    FROM NonGrouped
),
Ranked AS (
    SELECT
        DENSE_RANK() OVER (ORDER BY AmountAbs DESC) AS RowNumber,
        ShDate,
        TotalAccountCode,
        ISNULL(TotalAccountName, '') AS TotalAccountName,
        CertainAccountCode,
        ISNULL(CertainAccountName, '') AS CertainAccountName,
        SumBedeh AS Bedeh,
        SumBestan AS Bestan,
        '' AS note
    FROM AllData
    WHERE AmountAbs > 0
),
FinalResult AS (
  
    SELECT 
        RowNumber, ShDate, TotalAccountCode, 
        CAST(TotalAccountName AS NVARCHAR(90)) AS TotalAccountName,
        CertainAccountCode,
        CAST(CertainAccountName AS NVARCHAR(150)) AS CertainAccountName,
        Bedeh, 0 AS Bestan, note, 1 AS SortOrder
    FROM Ranked
    WHERE Bedeh > 0

    UNION ALL


    SELECT 
        RowNumber, ShDate, TotalAccountCode, 
        CAST(TotalAccountName AS NVARCHAR(90)) AS TotalAccountName,
        CertainAccountCode,
        CAST(CertainAccountName AS NVARCHAR(150)) AS CertainAccountName,
        0 AS Bedeh, Bestan, note, 2 AS SortOrder
    FROM Ranked
    WHERE Bestan > 0
)
SELECT
    RowNumber,
    ShDate,
    TotalAccountCode,
    TotalAccountName,
    CertainAccountCode,
    CertainAccountName,
    Bedeh,
    Bestan,
    note
FROM FinalResult
ORDER BY 
    RowNumber,
  
    SortOrder; 
    END;
END

